package com.github.dengmin.mysql.dao.impl;

import com.github.dengmin.mysql.common.SQL;
import com.github.dengmin.mysql.dao.DatabaseConnectionDao;
import com.github.dengmin.mysql.mapper.ConnectionRowMapper;
import com.github.dengmin.mysql.model.DatabaseConnection;
import com.github.dengmin.mysql.model.Pagination;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * @Author dengmin
 * @Created 2020/6/23 下午1:37
 */
@Repository
public class DatabaseConnectionDaoImpl implements DatabaseConnectionDao {
    private static final long serialVersionUID = 1L;
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public Pagination<DatabaseConnection> pagination(int limit, int offset) {
        String sql = "select * from database_connection limit %d, %d";
        sql = String.format(sql, limit, offset);
        List<DatabaseConnection> data = jdbcTemplate.query(sql, new ConnectionRowMapper());
        Pagination<DatabaseConnection> page = new Pagination<>();
        page.setData(data);
        page.setTotal(jdbcTemplate.queryForObject("select count(1) from database_connection", Integer.class));
        return page;
    }

    /**
     * 保存　返回自增id
     * @param dbConnection
     * @return
     */
    @Override
    public int save(DatabaseConnection dbConnection) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pstmt = connection.prepareStatement(SQL.insertConnection, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, dbConnection.getName());
                pstmt.setString(2, dbConnection.getHost());
                pstmt.setInt(3, dbConnection.getPort());
                pstmt.setString(4, dbConnection.getUsername());
                pstmt.setString(5, dbConnection.getPassword());
                pstmt.setString(6, dbConnection.getDbName());
                return pstmt;
            }
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public DatabaseConnection getById(int resourceId){
        return jdbcTemplate.queryForObject(SQL.queryConnection, new Object[]{resourceId}, new ConnectionRowMapper());
    }

    public void deleteById(Integer id){
        jdbcTemplate.update("delete from database_connection where id = ?", id);
    }
}
